AlchemyJ by Axisoft

5.0

ajSwiftMXToJSON function

Available since AlchemyJ v5.0

Description

The ajSwiftMXToJSON function can convert the SWIFT MX message to JSON string, it only supports ISO 20022 standard. This is an example for SWIFT MX message.

<?xml version="1.0" encoding="UTF-8" ?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03">
    <CstmrCdtTrfInitn>
        <GrpHdr>
            <MsgId>Message-Id</MsgId>
            <CreDtTm>2024-05-10T16:10:02.017+00:00</CreDtTm>
            <NbOfTxs>1</NbOfTxs>
            <CtrlSum>510.24</CtrlSum>
            <InitgPty>
                <Id>
                    <OrgId>
                        <Othr>
                            <Id>Client-Id</Id>
                        </Othr>
                    </OrgId>
                </Id>
            </InitgPty>
        </GrpHdr>
        <PmtInf>
            <PmtInfId>Batch-Id</PmtInfId>
            <PmtMtd>TRF</PmtMtd>
            <PmtTpInf>
                <SvcLvl>
                    <Cd>SEPA</Cd>
                </SvcLvl>
            </PmtTpInf>
            <ReqdExctnDt>2024-05-10</ReqdExctnDt>
            <Dbtr>
                <Nm>Debtor Account Holder Name</Nm>
            </Dbtr>
            <DbtrAcct>
                <Id>
                    <IBAN>IBAN</IBAN>
                </Id>
            </DbtrAcct>
            <DbtrAgt>
                <FinInstnId>
                    <BIC>Bank BIC</BIC>
                </FinInstnId>
            </DbtrAgt>
            <CdtTrfTxInf>
                <PmtId>
                    <EndToEndId>End-to-End-Id</EndToEndId>
                </PmtId>
                <Amt>
                    <InstdAmt Ccy="EUR">510.24</InstdAmt>
                </Amt>
                <CdtrAgt>
                    <FinInstnId>
                        <BIC>Bank BIC</BIC>
                    </FinInstnId>
                </CdtrAgt>
                <Cdtr>
                    <Nm>Creditor Account Holder Name</Nm>
                </Cdtr>
                <CdtrAcct>
                    <Id>
                        <IBAN>IBAN</IBAN>
                    </Id>
                </CdtrAcct>
            </CdtTrfTxInf>
        </PmtInf>
    </CstmrCdtTrfInitn>
</Document>

Syntax

ajSwiftMXToJSON(xml_text,[version],[run_condition],[run_by_function_point_only])

Argument NameArgument TypeDescription
xml_text
(required)		RangeSpecify content of the FIX message from a range of cells.
The range of cells can span multiple rows and columns, and the content in cells are concatenated into one string from left to right and top to bottom.
version
(optional)		StringSupport ISO 20022 standard.
Such as pain.001.001.03
run_condition
(optional)		BooleanThe function will run when the value is TRUE. Otherwise will not run.

The default value is TRUE.
run_by_function_point_only
(optional)		BooleanIf it equals FALSE, the function can be executed through 'Excel Calculation' (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point.

If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only.



The default value is TRUE.

The function will return:

1) Return Value: JSON String.

2) Return Type: Single Value / Multiple values (array formula).

Example

Make sure the AlchemyJ Function Proxy was started up when executing ajSwiftMXToJSON in the AlchemyJ workbook. You can start the proxy from More Tools - Run AlchemyJ Function Proxy.

Example 1

We use the above SWIFT MX message as an example.

=ajSwiftMXToJSON(B1,B2,B3,B4)

The result as below

{
  "cstmrCdtTrfInitn": {
    "grpHdr": {
      "msgId": "Message-Id",
      "creDtTm": {
        "dateTime": {
          "date": {
            "year": 2024,
            "month": 5,
            "day": 10
          },
          "time": {
            "hour": 16,
            "minute": 10,
            "second": 2,
            "nano": 17000000
          }
        },
        "offset": {
          "totalSeconds": 0
        }
      },
      "nbOfTxs": "1",
      "ctrlSum": 510.24,
      "initgPty": {
        "id": {
          "orgId": {
            "othr": [
              {
                "id": "Client-Id"
              }
            ]
          }
        }
      }
    },
    "pmtInf": [
      {
        "pmtInfId": "Batch-Id",
        "pmtMtd": "TRF",
        "pmtTpInf": {
          "svcLvl": {
            "cd": "SEPA"
          }
        },
        "reqdExctnDt": {
          "year": 2024,
          "month": 5,
          "day": 10
        },
        "dbtr": {
          "nm": "Debtor Account Holder Name"
        },
        "dbtrAcct": {
          "id": {
            "iban": "IBAN"
          }
        },
        "dbtrAgt": {
          "finInstnId": {
            "bic": "Bank BIC"
          }
        },
        "cdtTrfTxInf": [
          {
            "pmtId": {
              "endToEndId": "End-to-End-Id"
            },
            "amt": {
              "instdAmt": {
                "value": 510.24,
                "ccy": "EUR"
              }
            },
            "cdtrAgt": {
              "finInstnId": {
                "bic": "Bank BIC"
              }
            },
            "cdtr": {
              "nm": "Creditor Account Holder Name"
            },
            "cdtrAcct": {
              "id": {
                "iban": "IBAN"
              }
            }
          }
        ]
      }
    ]
  },
  "type": "MX",
  "@xmlns": "urn:iso:std:iso:20022:tech:xsd:pain.001.001.03",
  "identifier": "pain.001.001.03"
}

Example 2

When the content of message is very large,you can put it into different cells, if the output exceed the maximum value of a cell, it should be an array formula.

=ajSwiftMXToJSON(B1:B2,B3,B4,B5)

Note:

  1. In this sample, you need to select the range of cells B8:C8 first, then input the formula, press Ctrl + Shift keys, then press Enter key.

  2. If you want to covert the JSON back to the SWIFT MX message you can use ajSwiftJSONToMX function to implement that.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
The format of xml string is incorrect.
SWIFT MX message version is not support.
← ajSwiftJSONToMXajSystemError →